"""add login registration structures

Revision ID: 2e781c38db05
Revises: f6229d3451be
Create Date: 2025-10-22 09:48:57.650706

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


# revision identifiers, used by Alembic.
revision = "2e781c38db05"
down_revision = "f6229d3451be"
branch_labels = None
depends_on = None

USER_TYPE_ENUM = sa.Enum("patient", "doctor", "admin", name="user_type")
VERIFICATION_STATUS_ENUM = sa.Enum("pending", "approved", "rejected", name="verification_status")
DOCUMENT_TYPE_ENUM = sa.Enum("id_card", "passport", name="document_type")
GENDER_ENUM = sa.Enum("male", "female", name="gender")
VERIFICATION_PURPOSE_ENUM = sa.Enum("register", "reset_password", name="verification_purpose")


def upgrade() -> None:
    bind = op.get_bind()

    USER_TYPE_ENUM.create(bind, checkfirst=True)
    VERIFICATION_STATUS_ENUM.create(bind, checkfirst=True)
    DOCUMENT_TYPE_ENUM.create(bind, checkfirst=True)
    GENDER_ENUM.create(bind, checkfirst=True)
    VERIFICATION_PURPOSE_ENUM.create(bind, checkfirst=True)

    # Extend users table
    op.add_column("users", sa.Column("phone", sa.String(length=20), nullable=True))
    op.add_column(
        "users",
        sa.Column(
            "user_type",
            USER_TYPE_ENUM,
            nullable=False,
            server_default="patient",
        ),
    )
    op.add_column(
        "users",
        sa.Column(
            "verification_status",
            VERIFICATION_STATUS_ENUM,
            nullable=False,
            server_default="approved",
        ),
    )
    op.add_column("users", sa.Column("full_name", sa.String(length=100), nullable=True))
    op.add_column(
        "users",
        sa.Column("document_type", DOCUMENT_TYPE_ENUM, nullable=True),
    )
    op.add_column(
        "users",
        sa.Column("document_number", sa.String(length=32), nullable=True),
    )
    op.add_column("users", sa.Column("birth_date", sa.Date(), nullable=True))
    op.add_column("users", sa.Column("agreed_at", sa.DateTime(), nullable=True))
    op.add_column("users", sa.Column("last_login_at", sa.DateTime(), nullable=True))

    # Convert gender column to enum
    op.add_column("users", sa.Column("gender_tmp", GENDER_ENUM, nullable=True))
    op.execute(
        """
        UPDATE users
        SET gender_tmp = CASE
            WHEN gender IN ('male', '男', 'M', 'm', '1') THEN 'male'
            WHEN gender IN ('female', '女', 'F', 'f', '0') THEN 'female'
            ELSE NULL
        END
        """
    )
    op.drop_column("users", "gender")
    op.alter_column("users", "gender_tmp", new_column_name="gender")

    # Populate phone for existing records and enforce NOT NULL
    op.execute(
        """
        UPDATE users
        SET phone = COALESCE(phone, username, email, '0000000000')
        """
    )
    op.alter_column("users", "phone", nullable=False, existing_type=sa.String(length=20))

    # Allow nullable email/username
    op.alter_column(
        "users",
        "email",
        existing_type=sa.String(length=255),
        nullable=True,
    )
    op.alter_column(
        "users",
        "username",
        existing_type=sa.String(length=100),
        nullable=True,
    )

    # Remove fields moved to profiles
    op.drop_column("users", "height")
    op.drop_column("users", "weight")
    op.drop_column("users", "bmi")

    # Unique constraints
    op.create_unique_constraint("uq_users_phone", "users", ["phone"])
    op.create_unique_constraint("uq_users_document_number", "users", ["document_number"])

    # Create patient_profiles table
    op.create_table(
        "patient_profiles",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("height_cm", sa.Float(), nullable=True),
        sa.Column("weight_kg", sa.Float(), nullable=True),
        sa.Column("bmi", sa.Float(), nullable=True),
        sa.Column("emergency_contact", sa.String(length=50), nullable=True),
        sa.Column("emergency_phone", sa.String(length=20), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("user_id", name="uq_patient_profiles_user_id"),
    )

    # Create doctor_profiles table
    op.create_table(
        "doctor_profiles",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("user_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("qualification_certificate_no", sa.String(length=64), nullable=True),
        sa.Column("registration_certificate_no", sa.String(length=64), nullable=True),
        sa.Column("hospital_name", sa.String(length=128), nullable=True),
        sa.Column("department", sa.String(length=64), nullable=True),
        sa.Column("title", sa.String(length=64), nullable=True),
        sa.Column("audit_comment", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("user_id", name="uq_doctor_profiles_user_id"),
    )

    # Create doctor_certificate_files table
    op.create_table(
        "doctor_certificate_files",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("profile_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("file_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(["file_id"], ["uploaded_files.id"], ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["profile_id"], ["doctor_profiles.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("profile_id", "file_id", name="uq_doctor_certificate_files_profile_file"),
    )

    # Create verification_codes table
    op.create_table(
        "verification_codes",
        sa.Column("id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("phone", sa.String(length=20), nullable=False),
        sa.Column("code", sa.String(length=6), nullable=False),
        sa.Column("purpose", VERIFICATION_PURPOSE_ENUM, nullable=False),
        sa.Column("is_used", sa.Boolean(), nullable=False, server_default=sa.text("FALSE")),
        sa.Column("expires_at", sa.DateTime(), nullable=False),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("updated_at", sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("ix_verification_codes_phone", "verification_codes", ["phone"])

    # Remove server defaults after data migration
    op.alter_column("users", "user_type", server_default=None)
    op.alter_column("users", "verification_status", server_default=None)


def downgrade() -> None:
    # Drop verification tables first
    op.drop_index("ix_verification_codes_phone", table_name="verification_codes")
    op.drop_table("verification_codes")

    op.drop_table("doctor_certificate_files")
    op.drop_table("doctor_profiles")
    op.drop_table("patient_profiles")

    # Restore dropped columns
    op.add_column("users", sa.Column("bmi", sa.FLOAT(), nullable=True))
    op.add_column("users", sa.Column("weight", sa.FLOAT(), nullable=True))
    op.add_column("users", sa.Column("height", sa.FLOAT(), nullable=True))

    # Recreate gender column as string
    op.add_column("users", sa.Column("gender_tmp", sa.String(length=10), nullable=True))
    op.execute(
        """
        UPDATE users
        SET gender_tmp = CASE
            WHEN gender = 'male' THEN 'male'
            WHEN gender = 'female' THEN 'female'
            ELSE NULL
        END
        """
    )
    op.drop_column("users", "gender")
    op.alter_column("users", "gender_tmp", new_column_name="gender")

    # Drop new constraints
    op.drop_constraint("uq_users_document_number", "users", type_="unique")
    op.drop_constraint("uq_users_phone", "users", type_="unique")

    # Drop new columns
    op.drop_column("users", "last_login_at")
    op.drop_column("users", "agreed_at")
    op.drop_column("users", "birth_date")
    op.drop_column("users", "document_number")
    op.drop_column("users", "document_type")
    op.drop_column("users", "full_name")
    op.drop_column("users", "verification_status")
    op.drop_column("users", "user_type")
    op.drop_column("users", "phone")

    # Restore gender column to original type
    # Revert nullable constraints
    op.alter_column(
        "users",
        "username",
        existing_type=sa.String(length=100),
        nullable=False,
    )
    op.alter_column(
        "users",
        "email",
        existing_type=sa.String(length=255),
        nullable=False,
    )

    # Drop enum types
    VERIFICATION_PURPOSE_ENUM.drop(op.get_bind(), checkfirst=True)
    GENDER_ENUM.drop(op.get_bind(), checkfirst=True)
    DOCUMENT_TYPE_ENUM.drop(op.get_bind(), checkfirst=True)
    VERIFICATION_STATUS_ENUM.drop(op.get_bind(), checkfirst=True)
    USER_TYPE_ENUM.drop(op.get_bind(), checkfirst=True)
